from datetime import timedelta

import pandas as pd
import os
import numpy as np

repo_name_list = ["cmssw", "django", "kubernetes",  "moby", "opencv", "pandas", "rails", "react", "rust",
                  "salt", "scikit-learn", "symfony", "tensorflow", "terraform", "yii2"]
all_alg_list = [
    "original"
]
for repo_name in repo_name_list:
    for alg_name in all_alg_list:
        data_file_name = "../simulation/sim_data/" + repo_name + "/"+repo_name+"_pr_mp.xlsx"

        # 读取xlsx文件
        data_df = pd.read_excel(data_file_name)
        data_df['created_time'] = pd.to_datetime(data_df['created_time'], format="%Y-%m-%d %H:%M:%S", dayfirst=True,
                                              utc=True).dt.tz_localize(None)
        data_df['closed_time'] = pd.to_datetime(data_df['closed_time'], format="%Y-%m-%d %H:%M:%S", dayfirst=True,
                                             utc=True).dt.tz_localize(None)
        data_df['merged_time'] = pd.to_datetime(data_df['merged_time'], format="%Y-%m-%d %H:%M:%S", dayfirst=True,
                                             utc=True).dt.tz_localize(None)
        # 1. 从“模型时间”和“每日积压”列获取所有的数据，其中模型时间从1开始代表着从2022-01-01开始，每日积压代表着每日的积压量，将二者用dict保存

        start_day = pd.to_datetime("2022-01-01")
        end_day = pd.to_datetime("2022-12-31")
        re_count_byday = {}
        original_open = "original_open"
        sim_open="sim_open"

        # 从start_day到end_day的每天的时间戳 初始化re_comment_count_byday
        for i in range((end_day - start_day).days + 1):
            day = start_day + timedelta(days=i)
            re_count_byday[str(day)] = {}
            re_count_byday[str(day)][original_open] = 0

        for index, row in data_df.iterrows():
            created_time = str(row['created_time'].date()) + " 00:00:00"
            closed_time = str(row['closed_time'].date()) + " 00:00:00"
            merged_time = str(row['merged_time'].date()) + " 00:00:00"
            pr_start_day = row['created_time'].date()

            if row['closed_time'].date() is not None and pd.isna(row['closed_time'].date()) is False:
                pr_end_day = row['closed_time'].date()
            else:
                pr_end_day = end_day.date()
            # 从创建到关闭的每天的PR数加1
            for i in range((pr_end_day - pr_start_day).days + 1):
                day = pr_start_day + timedelta(days=i)
                day = str(day) + " 00:00:00"
                if day in re_count_byday.keys():
                    re_count_byday[day][original_open] = re_count_byday[day][original_open] + 1

        sim_file_name = "./Process Simulation/Results/" + repo_name + "/Results/" + alg_name + "_result_old.xlsx"

        # 读取xlsx文件
        sim_df = pd.read_excel(sim_file_name)

        # 1. 从“模型时间”和“每日积压”列获取所有的数据，其中模型时间从1开始代表着从2022-01-01开始，每日积压代表着每日的积压量，将二者用dict保存
        daily_backlog = sim_df['每日积压'].tolist()
        # 遍历模拟数据中每天的积压量
        for day_num, backlog in enumerate(sim_df['每日积压']):
            # 将数字转换为对应的日期（假设第一天是2022-01-01）
            model_day = start_day + timedelta(days=day_num)  # 减一是因为enumerate从0开始计数
            day_str = str(model_day.date()) + " 00:00:00"

            # 如果该日期在初始化过的范围内，则累加积压量
            if day_str in re_count_byday:
                re_count_byday[day_str][sim_open] = backlog



        # 将re_count_byday转换为DataFrame并写入xlsx文件
        data_list = []
        for date, values in re_count_byday.items():
            data_list.append([date] + list(values.values()))

        df_result = pd.DataFrame(data_list, columns=['date', 'real_open', 'simulation_open'])


        # 写入xlsx文件
        output_file_name = "./Process Simulation/Results/" + repo_name + "/V&V/" + repo_name +"_real_simulation_accumulated.xlsx"
        df_result.to_excel(output_file_name, index=False)


for repo_name in repo_name_list:
    for alg_name in all_alg_list:
        data_file_name = "./Process Simulation/Results/" + repo_name + "/V&V/" + repo_name +"_real_simulation_accumulated.xlsx"
        df = pd.read_excel(data_file_name)
        df = df[df['real_open'] != 0]
        df = df[['date', 'real_open', 'simulation_open']]
        real_values = df['real_open'].to_numpy()
        predicted_values = df['simulation_open'].to_numpy()
        df['diff'] = df['simulation_open'] - df['real_open']
        MSPE = np.mean(np.square((predicted_values - real_values) / real_values))
        MMRE = np.mean(np.abs((predicted_values - real_values) / real_values))
        # df['MSPE'] = round(MSPE, 3)
        df['MMRE'] = round(MMRE, 3)
        df.columns = df.columns.map(lambda x: 'date' if x == 'Unnamed: 0' else x)

        # 保留第五列第一行数据，其余设为空
        df.iloc[1:, 4] = None
        print(df)
        df.to_excel(data_file_name, index=False)

